const express = require('express');
const mysql = require('mysql');
const { spawn } = require('child_process');
const bodyParser = require('body-parser');
const app = express();
const url = require('url');
const querystring = require('querystring');
const mime = require('mime');
const fs = require('fs');
const path = require('path');
const multer = require('multer');
const xlsx = require("node-xlsx");

// 创建MySQL连接池
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'fightingdxj12',
    database: 'labor_data',
    connectionLimit: 10
});
// 使用body-parser中间件解析POST请求的参数
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

app.get('/test',(req,res)=>{
	res.send('hello')
})


// 查询数据
app.post('/manage/', (req, res) => {
    //const query = url.parse(req.url).query;
    //const params = querystring.parse(query);
    //console.log("url为："+req.url);
    const sql = req.body.sql
    //console.log(req.body)
    pool.getConnection((err, connection) => {
        if (err) throw err;
        
        connection.query(sql, (err, result) => {
            connection.release();
            if (err) throw err;
		if(!Array.isArray(result)){
		const jsonString = JSON.stringify(result);
		const arrayString = `[${jsonString}]`;
		const jsonArray = JSON.parse(arrayString);
		console.log(jsonArray);
            	res.send(jsonArray);
		}else{

            	console.log(result);
	    	res.send(result);
		}
        });
    });
});

app.get('/login/', (req, res) => {
    const query = url.parse(req.url).query;
    const params = querystring.parse(query);
    const table = params.table
    const no = params.no
    const name = params.user
    const password = params.password
    //console.log("url为："+req.url);
    //console.log(params);
    pool.getConnection((err, connection) => {
        if (err) throw err;
	const sql = "select * from "+table+" where "+no+"=? and password=?"
        connection.query(sql, [name,password],(err, result) => {
            connection.release();
            if (err) throw err;
            console.log(result);
            res.send(result);
        });
    });
});

//下载文件
app.get('/schoolapp/download/:filename', (req, res) => {
  const fileName = req.params.filename;
  const filePath = path.join(__dirname, '/schoolapp/download', filename);
  const fileMime = mime.getType(filePath);

  fs.readFile(filePath, (err, data) => {
    if (err) {
      res.status(500).send('Error');
    } else {
      res.setHeader('Content-disposition', 'attachment; filename=' + fileName);
      res.setHeader('Content-type', fileMime);
      res.send(data);
    }
  });
});

// 配置Multer中间件
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        const reqPath = req.body.filePath
        const filePath = path.join(__dirname, 'schoolApp',reqPath);
        if (!fs.existsSync(filePath)) {
            fs.mkdirSync(filePath, { recursive: true });
        }
        cb(null, filePath);
    },
    filename: (req, file, cb) => {
	    path.extname(file.originalname)//获取扩展名
        cb(null, file.originalname);
    }
});
const upload = multer({ storage: storage });

// 处理上传请求
app.post('/schoolapp', upload.single('file'), (req, res) => {
	console.log(req.file)
    res.send(req.file);
});

app.get('/excel/', (req, res) => {
    const query = url.parse(req.url).query;
    const params = querystring.parse(query);
    const sql = params.sql
    //console.log(req)
    const data = [['sno','sname','sd','sgrade','smajor','stype','count']]
    //console.log("url为："+req.url);
    //console.log(params);
    pool.getConnection((err, connection) => {
        if (err) throw err;
        //const sql=""
        connection.query(sql, (err,rows, result) => {
            connection.release();
            if (err) throw err;
            console.log(result);
	    if(rows)
		{
			for(var i = 0; i < rows.length; i++)
			{
				var arr=[];
				var value=rows[i];
				for(var j in value){
					arr.push(value[j]);
				}
				data.push(arr);
			}
		}
		var buffer = xlsx.build([
			{
				name:'sheet1',
				data:data
			}
		]);
		/*res.set({
			// 告诉浏览器这是一个二进制文件
                        'Content-Type': 'application/octet-stream',
			// 告诉浏览器这是一个需要下载的文件并且文件名为Asnull.xlsx
                        'Content-Disposition': 'attachment; filename=sheet1.xlsx' 
               });*/
	       res.setHeader('Content-Disposition', 'attachment; filename=sheet1.xlsx');
               res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.send(buffer);
        });
    });
});

//静态获取图片
app.use('/schoolapp/headimg',express.static('/home/ubuntu/schoolApp/headimg'));
// 启动HTTP服务器
const server = app.listen(8900, () => {
    console.log('Server is running on port 8900');
});
